package com.sp2p.dao.admin;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang.StringEscapeUtils;
import org.apache.commons.lang.StringUtils;
import com.shove.data.DataException;
import com.shove.data.DataSet;
import com.shove.data.dao.MySQL;
import com.shove.util.BeanMapUtils;
import com.sp2p.database.Dao;
import com.sp2p.database.Dao.Tables;
import com.sp2p.database.Dao.Tables.t_group;

/**
 * @ClassName: AfterCreditManageDao.java
 * @Author: gang.lv
 * @Date: 2013-3-19 上午10:16:48
 * @Copyright: 2013 www.emis.com Inc. All rights reserved.
 * @Version: V1.0.1
 * @Descrb: 统计管理数据处理层
 */
public class StatisManageDao {

	public Map<String, String> queryBorrowStatisAmount(Connection conn,
			String borrowTitle, String borrower, String timeStart,
			String timeEnd, int borrowWayInt) throws SQLException, DataException {
		StringBuffer command = new StringBuffer();
		command.append("SELECT SUM(mediateFee) amount FROM v_t_borrow_statis where 1=1");
		if (StringUtils.isNotBlank(borrowTitle)) {
			command.append(" and  borrowTitle  like '%"+StringEscapeUtils.escapeSql(borrowTitle.trim())+"%' ");
		}
		if (StringUtils.isNotBlank(borrower)) {
			command.append(" and borrower  like '%"+ StringEscapeUtils.escapeSql(borrower.trim()) + "%'");
		}
		if (StringUtils.isNotBlank(timeStart)) {
			command.append(" and auditTime >= '" + StringEscapeUtils.escapeSql(timeStart) + "'");
		}
		if (StringUtils.isNotBlank(timeEnd)) {
			command.append(" and auditTime <= '" + StringEscapeUtils.escapeSql(timeEnd) + "'");
		}
		if (borrowWayInt != -1) {
			command.append(" and borrowWayYTF = " + borrowWayInt);
		}
		DataSet dataSet = MySQL.executeQuery(conn, command.toString());
		command=null;
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	
	public List<Map<String,String>> queryInvestStatisRankSize(Connection conn,String investor,
			String timeStart, String timeEnd, int groupInt,String order){
		StringBuffer condition = new StringBuffer();
		condition.append("select a.*,b.realSum from v_t_invest_rank a left join (select investor,sum(realAmount) realSum from v_t_invest_rank where 1=1 ");
		if (StringUtils.isNotBlank(investor)) {
			condition.append(" and a.investor like '%"
					+ StringEscapeUtils.escapeSql(investor.trim()) + "%'");
		}
		if (StringUtils.isNotBlank(timeStart)) {
			condition.append(" and a.investTime >= '"
					+ StringEscapeUtils.escapeSql(timeStart) + "'");
		}
		if (StringUtils.isNotBlank(timeEnd)) {
			condition.append(" and a.investTime <= '"
					+ StringEscapeUtils.escapeSql(timeEnd) + "'");
		}
		if (groupInt != -1) {
			condition.append(" and a.groupId = " + groupInt);
		}
		condition.append(" group by investor) b on a.investor = b.investor ");
		if("up".equals(order)){
			condition.append(" order by a.rating,b.realSum desc,a.realAmount desc ");
		}
		if("down".equals(order)){
			condition.append(" order by a.rating desc,b.realSum desc,a.realAmount desc ");
		}
		DataSet dataSet = MySQL.executeQuery(conn, condition.toString());
		dataSet.tables.get(0).rows.genRowsMap();
		return dataSet.tables.get(0).rows.rowsMap;
	}
	
	public Map<String,String> queryLoginStatisByConditionSize(Connection conn,String userName, String realName,
			String timeStart, String timeEnd,int isRealName,
			String zhuStart,String zhuEnd) throws DataException{
		StringBuffer condition = new StringBuffer();
		condition.append("select count(id) as totalUser,sum(loginCount) as totalLoginCount from v_t_login_statis where 1=1 ");
		if (StringUtils.isNotBlank(userName)) {
			condition.append(" and username  like '%"
					+ StringEscapeUtils.escapeSql(userName.trim()) + "%'");
		}
		if (StringUtils.isNotBlank(realName)) {
			condition.append(" and realname  like '%"
					+ StringEscapeUtils.escapeSql(realName.trim()) + "%'");
		}
		if (StringUtils.isNotBlank(timeStart)) {
			condition.append(" and lastDate >= '"
					+ StringEscapeUtils.escapeSql(timeStart) + "'");
		}
		if (StringUtils.isNotBlank(timeEnd)) {
			condition.append(" and lastDate <= '"
					+ StringEscapeUtils.escapeSql(timeEnd) + "'");
		}
		if (StringUtils.isNotBlank(zhuStart)) {
			condition.append(" and createTime >= '"
					+ StringEscapeUtils.escapeSql(zhuStart) + "'");
		}
		if (StringUtils.isNotBlank(zhuEnd)) {
			condition.append(" and createTime <= '"
					+ StringEscapeUtils.escapeSql(zhuEnd) + "'");
		}
		if (isRealName == 0 ){
			condition.append(" and (idNo='' or idNo is null) ");
		}
		if (isRealName == 1){
			condition.append(" and idNo!='' ");
		}
		DataSet dataSet = MySQL.executeQuery(conn, condition.toString());
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	
	public Map<String,String> queryInvestStatisByConditionSize(Connection conn,String htNo, String investor,
			String timeStart, String timeEnd, int borrowWayInt,
			int isAutoBidInt, int borrowStatusInt, int groupInt,String investRate,int incomeWayInt,
			int incomeStatusInt,String incomeDay, int borrowProduct) throws DataException{
		String view = "(SELECT m.* , (SELECT CASE WHEN group_concat(b.groupName) IS NULL THEN '普通投资人' ELSE"
            + " group_concat(b.groupName) END AS groupNames FROM t_group_user a"
            + " LEFT JOIN t_group b ON a.groupId = b.id WHERE a.userId = m.userId) AS groupNames, (SELECT group_concat(a.groupId)"
            + " AS groupIds  FROM t_group_user a LEFT JOIN t_group b ON a.groupId = b.id"
            + " WHERE a.userId = m.userId) AS groupIds FROM v_t_invest_statis m) v"
            ;
		StringBuffer condition = new StringBuffer();
		condition.append("select count(v.id) as totalSize, sum(v.realAmount) as totalRealAmount, sum(v.hasInterest) as totalHasInterest," +
				"sum(v.hasPrincipal) as totalHasPrincipal,sum(v.hasAll) as totalHasAll,sum(v.restInterest) as totalRestInterest," +
				"sum(v.restPrincipal) as totalRestPrincipal,sum(v.restAll) as totalRestAll,sum(v.recivedFI) as totalRecivedFI," +
				"sum(v.yqsy) as totalYqsy,sum(v.buchang) as totalBuchang from"+ view +" where 1=1 ");
		if (StringUtils.isNotBlank(htNo)) {
			condition.append(" and  v.agreementId  like '%"
					+ StringEscapeUtils.escapeSql(htNo.trim()) + "%' ");
		}
		if (StringUtils.isNotBlank(investor)) {
			condition.append(" and v.investor like '%"
					+ StringEscapeUtils.escapeSql(investor.trim()) + "%'");
		}
		if (StringUtils.isNotBlank(timeStart)) {
			condition.append(" and v.investTime >= '"
					+ StringEscapeUtils.escapeSql(timeStart) + "'");
		}
		if (StringUtils.isNotBlank(timeEnd)) {
			condition.append(" and v.investTime <= '"
					+ StringEscapeUtils.escapeSql(timeEnd) + "'");
		}
		if (borrowWayInt != -1) {
			condition.append(" and v.borrowWayYTF = " + borrowWayInt);
		}
		if (isAutoBidInt != -1) {
			condition.append(" and v.isAutoBid = " + isAutoBidInt);
		}
		if (borrowStatusInt != -1) {
			if(borrowStatusInt == 1){
				condition.append(" and v.borrowStatus not in (4,5) ");
			}else if(borrowStatusInt == 2){
				condition.append(" and v.borrowStatus in (4,5) ");
			}
		}
		if (groupInt != -1) {
			condition.append(" and groupIds like '%" + groupInt + "%'");
		}
		if (!investRate.equals("-1")){
			condition.append(" and v.annualRate = " + investRate );
		}
		if (incomeWayInt != -1){
			condition.append(" and v.paymentMode = " + incomeWayInt);
		}
		if (incomeStatusInt!=-1 && incomeStatusInt!=20 && incomeStatusInt!=21){
			condition.append(" and v.borrowStatus = " + incomeStatusInt);
		}
		if(incomeStatusInt==20){
			condition.append(" and v.borrowStatus = 4 and recivedFI = 0");
		}
		if(incomeStatusInt==21){
			condition.append(" and v.borrowStatus = 4 and recivedFI > 0");
		}
		if(!incomeDay.equals("-1")){
			condition.append(" and v.incomeDay = " + incomeDay);
		}
		if(borrowProduct != -1){
			condition.append(" and v.borrowProduct = " + borrowProduct);
		}
		DataSet dataSet = MySQL.executeQuery(conn, condition.toString());
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	
	public Map<String,String> queryBorrowStatisByConditionSize(Connection conn,String htNo,
			String borrower, String timeStart, String timeEnd,
			int borrowWayInt,String repayDay,String annualRate,String borrowStatus,String repayWay,
			String mediateFeeLv,int borrowProduct,int agent) throws DataException{
		StringBuffer condition = new StringBuffer();
		condition.append("select count(id) as totalSize,sum(borrowAmount) as totalBorrowAmount," +
				"sum(mediateFee) as totalMediateFee,sum(realAmount) as totalRealAmount,sum(whPrincipal) as totalWhPrincipal," +
				"sum(whInterest) as totalWhInterest,sum(whAll) as totalWhAll,sum(yhPrincipal) as totalYhPrincipal,sum(yhInterest) as totalYhInterest," +
				"sum(hasPI) as totalHasPI,sum(stillPI) as totalStillPI,sum(allFI) as totalAllFI,sum(buchang) as totalBuchang,sum(allInterest) as totalAllInterest" +
				" from v_t_borrow_statis where 1=1 ");
		if (StringUtils.isNotBlank(htNo)) {
			condition.append(" and  agreementId  like '%"
					+ StringEscapeUtils.escapeSql(htNo.trim()) + "%' ");
		}
		if (StringUtils.isNotBlank(borrower)) {
			condition.append(" and borrower  like '%"
					+ StringEscapeUtils.escapeSql(borrower.trim()) + "%'");
		}
		if (StringUtils.isNotBlank(timeStart)) {
			condition.append(" and auditTime >= '"
					+ StringEscapeUtils.escapeSql(timeStart) + "'");
		}
		if (StringUtils.isNotBlank(timeEnd)) {
			condition.append(" and auditTime <= '"
					+ StringEscapeUtils.escapeSql(timeEnd) + "'");
		}
		if (borrowWayInt != -1) {
			condition.append(" and borrowWayYTF = " + borrowWayInt);
		}
		if(!repayDay.equals("-1")){
			condition.append(" and repayDay = " + repayDay);
		}
		if(!annualRate.equals("-1")){
			condition.append(" and annualRate = " + annualRate);
		}
		if(!borrowStatus.equals("-1")&&!borrowStatus.equals("20")&&!borrowStatus.equals("21")){
			condition.append(" and borrowStatus = " + borrowStatus);
		}
		if(borrowStatus.equals("20")){
			condition.append(" and borrowStatus = 4 and allFI = 0 ");
		}
		if(borrowStatus.equals("21")){
			condition.append(" and borrowStatus = 4 and allFI !=0 ");
		}
		if(!repayWay.equals("-1")){
			condition.append(" and paymentMode = " + repayWay);
		}
		if(!mediateFeeLv.equals("-1")){
			condition.append(" and mediateFeeLv = " + mediateFeeLv);
		}
		if(borrowProduct != -1){
			condition.append(" and borrowProduct = " + borrowProduct);
		}
		if(agent > 0){
			condition.append(" and agent = " + agent);
		}
		DataSet dataSet = MySQL.executeQuery(conn, condition.toString());
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	
	/**
	 * 查询所有的用户组
	 * @return
	 * @throws DataException 
	 * @throws SQLException 
	 */
	public List<Map<String, Object>> queryAllGroup(Connection conn) throws SQLException, DataException {
		Dao.Tables.t_group t_group = new Dao().new Tables().new t_group();
		DataSet dataSet=t_group.open(conn, "id,groupName", " ", "", -1, -1);
		dataSet.tables.get(0).rows.genRowsMap();
		return dataSet.tables.get(0).rows.rowsMap;
	}

}
